// Parts Copyright 2008 Newgrove Consultants Limited
// Modified By John Diss 
// From original work:
//
// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

using System;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace MsSqlSpatial
{
    internal static class GeometryColumnsTable
    {
        internal static bool CheckGeometryColumnExists(SqlCommand command, string schemaName, string tableName,
                                                       string columnName)
        {
            return DatabaseHelper.CheckQueryExists(command, string.Format(
                                                                @"SELECT [F_GEOMETRY_COLUMN]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2"
                                                                )
                                                   , schemaName
                                                   , tableName
                                                   , columnName
                );
        }

        internal static void ExecuteDeleteMetaRecord(SqlCommand command, string schemaName, string tableName,
                                                     string columnName)
        {
            DatabaseHelper.BuildCommand(command,
                                        @"DELETE FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2"
                                        , schemaName
                                        , tableName
                                        , columnName
                );
            command.ExecuteNonQuery();
        }

        internal static void ExecuteAddMetaRecord(SqlCommand command, string schemaName, string tableName,
                                                  string columnName, int srid, string type)
        {
            ExecuteDeleteMetaRecord(command, schemaName, tableName, columnName);

            DatabaseHelper.BuildCommand(command, string.Format(
                                                     @"INSERT INTO [ST].[GEOMETRY_COLUMNS]
([F_TABLE_CATALOG], [F_TABLE_SCHEMA], [F_TABLE_NAME], [F_GEOMETRY_COLUMN], [COORD_DIMENSION], [SRID], [GEOMETRY_TYPE])
VALUES ('', '{0}','{1}','{2}',{3},{4},'{5}')"
                                                     , schemaName, tableName, columnName, 2, srid, type));
            command.ExecuteNonQuery();
        }

        internal static void ExecuteCreate(SqlCommand command)
        {
            if (!DatabaseHelper.CheckMetaTableExists(command, "GEOMETRY_COLUMNS"))
            {
                command.CommandText =
                    @"CREATE TABLE [ST].[GEOMETRY_COLUMNS](
[F_TABLE_CATALOG] [varchar](256) NOT NULL,
[F_TABLE_SCHEMA] [varchar](256) NOT NULL,
[F_TABLE_NAME] [varchar](256) NOT NULL,
[F_GEOMETRY_COLUMN] [varchar](256) NOT NULL,
[COORD_DIMENSION] [int] NOT NULL,
[SRID] [int] NOT NULL,
[GEOMETRY_TYPE] [varchar](30) NOT NULL
)";
                command.ExecuteNonQuery();
            }
        }

        internal static void ExecuteBackup(SqlCommand command)
        {
            if (DatabaseHelper.CheckMetaTableExists(command, "GEOMETRY_COLUMNS"))
            {
                string backupTableName = "ST_GEOMETRY_COLUMNS_BACKUP_" + DateTime.Now.ToString("yyyyMMdd_HHmm");
                command.CommandText = "SELECT * INTO " + backupTableName + " FROM [ST].[GEOMETRY_COLUMNS]";
                command.ExecuteNonQuery();
            }
        }

        internal static void ExecuteDeleteMetaRecords(SqlCommand command, string schemaName, string tableName)
        {
            DatabaseHelper.BuildCommand(command,
                                        @"DELETE FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0 
AND [F_TABLE_NAME] = @p1"
                                        , schemaName
                                        , tableName
                );
            command.ExecuteNonQuery();
        }

        internal static List<string> GetGeometryColumnsByTable(SqlCommand command, string schemaName, string tableName)
        {
            var columns = new List<string>();
            DatabaseHelper.BuildCommand(command,
                                        @"SELECT [F_GEOMETRY_COLUMN]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1"
                                        , schemaName
                                        , tableName
                );
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                    columns.Add(reader.GetString(0));
            }
            return columns;
        }

        internal static int GetSRID(SqlCommand command, string schemaName, string tableName, string columnName)
        {
            var columns = new List<string>();
            DatabaseHelper.BuildCommand(command,
                                        @"SELECT [SRID]
FROM [ST].[GEOMETRY_COLUMNS]
WHERE [F_TABLE_SCHEMA] = @p0
AND [F_TABLE_NAME] = @p1
AND [F_GEOMETRY_COLUMN] = @p2
"
                                        , schemaName
                                        , tableName
                                        , columnName
                );
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.Read())
                    return reader.GetInt32(0);
            }
            return -1;
        }
    }
}